DROP TRIGGER USERS_ID_TRI;
DROP SEQUENCE USERS_ID_SEQ;
DROP TABLE USERS;

--创建表
CREATE TABLE USERS(
ID INT,
NAME VARCHAR2(30) NOT NULL,
SEX CHAR(1) DEFAULT 1 CHECK(SEX=1 OR SEX=0),
BIR DATE CHECK(BIR BETWEEN TO_DATE('1960/1/1', 'YYYY/MM/DD') AND TO_DATE('2009-12-31', 'YYYY-MM-DD')),
TEL VARCHAR2(14),
EML VARCHAR2(30),
ADDR VARCHAR(200),
CONSTRAINT PK_USER_ID PRIMARY KEY (ID)
);

--创建序列
CREATE SEQUENCE USERS_ID_SEQ
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
NOCACHE;

--创建触发器
CREATE TRIGGER USERS_ID_TRI BEFORE
INSERT ON USERS FOR EACH ROW WHEN(NEW.ID IS NULL)
BEGIN
SELECT USERS_ID_SEQ.NEXTVAL INTO:NEW.ID FROM DUAL;
END;
